package com.utils;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URL;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;

import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.constants.Constants;
import com.crmeb.core.exception.CrmebException;
import com.zbkj.crmeb.export.vo.ExcelColumn;

import cn.hutool.core.convert.Convert;

/** excel操作类
 * @author Mr.zhang
 * @since 2020-04-17
 **/
public class ExcelUtil {
    private final static Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    private final static String EXCEL2003 = "xls";
    private final static String EXCEL2007 = "xlsx";

    private static String sheetName;

    public static String getSheetName() {
        return sheetName;
    }

    public static void setSheetName(String sheetName) {
        ExcelUtil.sheetName = sheetName;
    }

    public static String getFileName() {
        return fileName;
    }

    public static void setFileName(String fileName) {
        ExcelUtil.fileName = fileName;
    }

    private static String fileName;

    public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls){
        Field[] fields = cls.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null && annotation.col() > 0) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int col = 0;
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        col = annotation.col();
                    }
                    return col;
                })).collect(Collectors.toList());

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(getSheetName());

        AtomicInteger ai = new AtomicInteger();
        {
            XSSFRow row = sheet.createRow(ai.getAndIncrement());
            AtomicInteger aj = new AtomicInteger();
            //写入头部
            fieldList.forEach(field -> {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnName = "";
                if (annotation != null) {
                    columnName = annotation.value();
                }
                sheet.setColumnWidth(aj.get(),annotation.width() * 256);
                XSSFCell cell = row.createCell(aj.getAndIncrement());
                XSSFCellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                XSSFFont font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnName);
            });
        }
        if (CollectionUtils.isNotEmpty(dataList)) {
            dataList.forEach(t -> {
                XSSFRow row1 = sheet.createRow(ai.getAndIncrement());
                row1.setHeight((short) 1200);
                AtomicInteger aj = new AtomicInteger();
                fieldList.forEach(field -> {
                    Class<?> type = field.getType();
                    Object value = "";
                    try {
                        value = field.get(t);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    XSSFCell cell = row1.createCell(aj.getAndIncrement());
                    if (value != null) {
                        if (type == Date.class) {
                            cell.setCellValue(value.toString());
                        } if (type == BigDecimal.class){
                            cell.setCellValue(((BigDecimal)value).doubleValue());
                        } else{
                            /*ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                            if(annotation.isImg()){
                                try {
                                    // 显示图片
                                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                                    BufferedImage bufferImg = ImageIO.read(new URL(value.toString()));
                                    ImageIO.write(bufferImg, "JPG", byteArrayOut);
                                    XSSFClientAnchor anchor = new XSSFClientAnchor(400, 100, 700, 320, (short) 1, ai.get()-1, (short) 2, ai.get());
                                    //创建
                                    XSSFDrawing patri = sheet.createDrawingPatriarch();
                                    patri.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                                }catch (Exception e){
                                    e.printStackTrace();
                                }
                            }else {*/
                                cell.setCellValue(value.toString());
                            /*}*/
                        }
//                        cell.setCellValue(value.toString());
                    }
                });
            });
        }
        //冻结窗格
        wb.getSheet(getSheetName()).createFreezePane(0, 1, 0, 1);
        buildExcelDocument(getFileName() + "_" + DateUtil.nowDate(Constants.DATE_TIME_FORMAT_NUM) +".xlsx", wb, response);

//      buildExcelFile("/Users/maoyanwei/default.xlsx",wb);
//      buildExcelFile(getFileName() + "_" + DateUtil.nowDate(Constants.DATE_TIME_FORMAT_NUM) +".xlsx",wb);
    }


    public static <T> void writeExcelByIsImg(HttpServletResponse response, List<T> dataList, Class<T> cls,Boolean isImg){
        Field[] fields = cls.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null && annotation.col() > 0) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int col = 0;
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        col = annotation.col();
                    }
                    return col;
                })).collect(Collectors.toList());

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(getSheetName());

        AtomicInteger ai = new AtomicInteger();
        {
            XSSFRow row = sheet.createRow(ai.getAndIncrement());
            AtomicInteger aj = new AtomicInteger();
            //写入头部
            fieldList.forEach(field -> {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnName = "";
                if (annotation != null) {
                    columnName = annotation.value();
                }
                sheet.setColumnWidth(aj.get(),annotation.width() * 256);
                XSSFCell cell = row.createCell(aj.getAndIncrement());
                XSSFCellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                XSSFFont font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnName);
            });
        }
        if (CollectionUtils.isNotEmpty(dataList)) {
            for (T t : dataList) {
                XSSFRow row1 = sheet.createRow(ai.getAndIncrement());
                row1.setHeight((short) 1200);
                AtomicInteger aj = new AtomicInteger();
                for (Field field : fieldList) {
                    Class<?> type = field.getType();
                    Object value = "";
                    try {
                        value = field.get(t);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    XSSFCell cell = row1.createCell(aj.getAndIncrement());
                    if (value != null) {
                        if (type == Date.class) {
                            cell.setCellValue(value.toString());
                        } if (type == BigDecimal.class){
                            cell.setCellValue(((BigDecimal)value).doubleValue());
                        } else{
                            try {
                                if(field.getName().equals("image") && BooleanUtils.isTrue(isImg)){
                                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                                    int col = annotation.col();
                                    // 显示图片
                                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                                    BufferedImage bufferImg = ImageIO.read(new URL(value.toString()));
                                    ImageIO.write(bufferImg, "JPG", byteArrayOut);
                                    XSSFClientAnchor anchor = new XSSFClientAnchor(400, 100, 700, 320, (short) col-1, ai.get()-1, (short) col, ai.get());
                                    //创建
                                    XSSFDrawing patri = sheet.createDrawingPatriarch();
                                    patri.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                                }
                            }catch (Exception e){
                                e.printStackTrace();
                            }
                        }
                        cell.setCellValue(value.toString());
                    }
                }
            }
        }
        //冻结窗格
        wb.getSheet(getSheetName()).createFreezePane(0, 1, 0, 1);
        buildExcelDocument(getFileName() + "_" + DateUtil.nowDate(Constants.DATE_TIME_FORMAT_NUM) +".xlsx", wb, response);

//      buildExcelFile("/Users/maoyanwei/default.xlsx",wb);
//      buildExcelFile(getFileName() + "_" + DateUtil.nowDate(Constants.DATE_TIME_FORMAT_NUM) +".xlsx",wb);
    }

    /**
     * 浏览器下载excel
     * @param fileName
     * @param wb
     * @param response
     */

    private static void buildExcelDocument(String fileName, Workbook wb,HttpServletResponse response){
//        try {
//            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
//            response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
//            response.flushBuffer();
//            wb.write(response.getOutputStream());
//        } catch (IOException e) {
//            e.printStackTrace();
//        }

        try{
            OutputStream os = response.getOutputStream();
            // 重置输出流
            response.reset();
            // 设定输出文件头 ,    //要保存的文件名
            response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
            // 定义输出类型
            response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            wb.write(os);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成excel文件
     * @param path 生成excel路径
     * @param wb
     */
    private static  void  buildExcelFile(String path, Workbook wb){

        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
        try {
            wb.write(new FileOutputStream(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static <T> String writeExcelFile(List<T> dataList, Class<T> cls){
        Field[] fields = cls.getDeclaredFields();
        List<Field> fieldList = Arrays.stream(fields)
                .filter(field -> {
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null && annotation.col() > 0) {
                        field.setAccessible(true);
                        return true;
                    }
                    return false;
                }).sorted(Comparator.comparing(field -> {
                    int col = 0;
                    ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                    if (annotation != null) {
                        col = annotation.col();
                    }
                    return col;
                })).collect(Collectors.toList());

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(getSheetName());

        AtomicInteger ai = new AtomicInteger();
        {
            XSSFRow row = sheet.createRow(ai.getAndIncrement());
            AtomicInteger aj = new AtomicInteger();
            //写入头部
            fieldList.forEach(field -> {
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                String columnName = "";
                if (annotation != null) {
                    columnName = annotation.value();
                }
                sheet.setColumnWidth(aj.get(),annotation.width() * 256);
                XSSFCell cell = row.createCell(aj.getAndIncrement());
                XSSFCellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

                XSSFFont font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnName);

            });
        }
        if (CollectionUtils.isNotEmpty(dataList)) {
            dataList.forEach(t -> {
                XSSFRow row1 = sheet.createRow(ai.getAndIncrement());
                row1.setHeight((short) 1200);
                AtomicInteger aj = new AtomicInteger();
                fieldList.forEach(field -> {
                    Class<?> type = field.getType();
                    Object value = "";
                    try {
                        value = field.get(t);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    XSSFCell cell = row1.createCell(aj.getAndIncrement());
                    if (value != null) {
                        if (type == Date.class) {
                            cell.setCellValue(value.toString());
                        } else {
                            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                            if(annotation.isImg()){
                                try {
                                    // 显示图片
                                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                                    BufferedImage bufferImg = ImageIO.read(new URL(value.toString()));
                                    ImageIO.write(bufferImg, "JPG", byteArrayOut);
                                    //设置图片大小，位置 400, 30, 700, 220
                                    XSSFClientAnchor anchor = new XSSFClientAnchor(400, 100, 700, 320, (short) 1, ai.get()-1, (short) 2, ai.get());
                                    //创建
                                    XSSFDrawing patri = sheet.createDrawingPatriarch();
                                    patri.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                                }catch (Exception e){
                                    e.printStackTrace();
                                }
                            }else {
                                cell.setCellValue(value.toString());
                            }
                        }
//                        cell.setCellValue(value.toString());
                    }
                });
            });
        }
        //冻结窗格
        wb.getSheet(getSheetName()).createFreezePane(0, 1, 0, 1);
        //浏览器下载excel
        //生成excel文件
        buildExcelFile("/Users/maoyanwei/default.xlsx",wb);
        return "";
    }

    /**
     * 对excel表单默认第一个索引名转换成list
     *
     * @param is 输入流
     * @return 转换后集合
     */
    public static <T> List<T> importExcel(InputStream is,Class<T> clazz) throws Exception {
        Workbook workbook = WorkbookFactory.create(is);
        List<T> list = new ArrayList<>();
        // 默认指向第1个sheet.
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            throw new CrmebException("文件sheet不存在");
        }
        int rows = sheet.getPhysicalNumberOfRows();
        if (rows > 0) {
            if(rows == 1){
                throw new CrmebException("表格内容不能为空");
            }
            // 定义一个map用于存放excel列的序号和field.
            Map<String, Integer> cellMap = new HashMap<>();
            // 获取表头
            Row heard = sheet.getRow(0);
            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {
                Cell cell = heard.getCell(i);
                if (Objects.nonNull(cell)) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String value = cell.getStringCellValue().trim();
                    cellMap.put(value, i);
                } else {
                    cellMap.put(null, i);
                }
            }
            // 有数据时才处理 得到类的所有field.
            Field[] allFields = clazz.getDeclaredFields();
            // 定义一个map用于存放列的序号和field.
            Map<Integer, Field> fieldsMap = new HashMap<>();
            for (Field field : allFields) {
                ExcelColumn attr = field.getAnnotation(ExcelColumn.class);
                if (attr != null) {
                    // 设置类的私有字段属性可访问.
                    field.setAccessible(true);
                    Integer column = cellMap.get(attr.value());
                    if (column != null) {
                        fieldsMap.put(column, field);
                    }else {
                        throw new CrmebException("文件表头定义异常;列:"+ (fieldsMap.size()+1) +",应填内容:"+attr.value());
                    }
                }
            }
            for (int i = 1; i < rows; i++) {
                // 从第2行开始取数据,默认第一行是表头.
                Row row = sheet.getRow(i);
                T entity = null;
                List<Object> exList = new ArrayList<>();
                for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) {
                    Object val = null;
                    Cell cell = row.getCell(entry.getKey());
                    if(cell != null){
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        val = cell.getStringCellValue();
                    }
                    // 如果不存在实例则新建.
                    entity = (entity == null ? clazz.newInstance() : entity);
                    // 从map中得到对应列的field.
                    Field field = fieldsMap.get(entry.getKey());
                    // 取得类型,并根据对象类型设置值.
                    Class<?> fieldType = field.getType();
                    if(Objects.nonNull(val)){
                        //强转错误设置为null
                        try {
                            if (String.class == fieldType) {
                                val = Convert.toStr(val);
                            } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {
                                val = Convert.toInt(val);
                            } else if (Long.TYPE == fieldType || Long.class == fieldType) {
                                val = Convert.toLong(val);
                            } else if (Double.TYPE == fieldType || Double.class == fieldType) {
                                val = Convert.toDouble(val);
                            } else if (Float.TYPE == fieldType || Float.class == fieldType) {
                                val = Convert.toFloat(val);
                            } else if (BigDecimal.class == fieldType) {
                                val = Convert.toBigDecimal(val);
                            } else if (Date.class == fieldType) {
                                val = DateUtils.parseDate(val.toString());
                            } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {
                                val = Convert.toBool(val, false);
                            }else {
                                val = null;
                            }
                        }catch (Exception e){
                            log.error("-----------表格数据异常:{} ,行:{},列:{}--------------",val,i+1,cell.getColumnIndex());
                            val = null;
                        }
                    }
                    String propertyName = field.getName();
                    ReflectUtils.invokeSetter(entity, propertyName, val);
                    exList.add(val);
                }
                List<Object> collect = exList.stream().filter(e -> Objects.nonNull(e) && StringUtils.isNotBlank(e.toString().trim())).collect(Collectors.toList());
                if(CollectionUtils.isNotEmpty(collect)){
                    list.add(entity);
                }
            }
        }
        return list;
    }

}